package com.xj.mysql;

import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * User: bjxiajun
 * Date: 14-2-14
 * Time: 上午10:12
 */
public class ItvDataProcess {
    private static Logger log = Logger.getLogger(ItvDataProcess.class);
    private static String url = "jdbc:mysql://192.168.173.52:3306/itv";
    private static Connection conn;
    private AtomicInteger actorId = new AtomicInteger(0);
    private Map<String, Integer> actorMap = new HashMap<String, Integer>();
    private List<Movie2actor> movie2actor = new ArrayList<Movie2actor>();

    static {
        try {
            Class clazz = Class.forName("com.mysql.jdbc.Driver");
            clazz.newInstance();
            conn = DriverManager.getConnection(url, "root", "123456");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void find(String sql) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            String id = rs.getString(1);
            String actor = rs.getString(2);
            processActor(id, actor);
        }
        stmt.close();
        rs.close();
        insertActor();
        insertM2A();
    }

    private void processActor(String mid, String actor) {
        if (actor != null && !actor.equals("")) {
            String[] actors = actor.split("\\s");
            for (int i = 0; i < actors.length; i++) {
                int rid;
                String name = actors[i];
                name = name.length() > 32 ? name.substring(0, 32) : name;
                if (actorMap.containsKey(name)) {
                    rid = actorMap.get(name);
                } else {
                    rid = actorId.incrementAndGet();
                    actorMap.put(name, rid);
                }
                movie2actor.add(new Movie2actor(mid, rid));
            }
        }
    }

    private void insertActor() throws SQLException {
        conn.setAutoCommit(false);
        int i = 0;
        StringBuffer sb = new StringBuffer();
        for (Map.Entry<String, Integer> e : actorMap.entrySet()) {
            String name = e.getKey();
            int id = e.getValue();
            /*PreparedStatement ps = conn.prepareStatement("insert into actor (id,name) values(?,?)");
            ps.setInt(1, id);
            ps.setString(2, name);
            ps.executeUpdate();
            ps.close();*/
            name=name.replace("'","\\'");
            sb.append("(" + id + ",'" + name + "'),");
            if (i % 200 == 0) {
                //conn.commit();
                log.info("insert into actor (id,name) values" + sb.substring(0, sb.length() - 1)+";");
                sb = new StringBuffer();
            }
            i++;
        }
        //conn.commit();
        log.info("insert into actor (id,name) values" + sb.substring(0, sb.length() - 1)+";");
    }

    private void insertM2A() throws SQLException {
        conn.setAutoCommit(false);
        int i = 1;
        StringBuffer sb = new StringBuffer();
        for (Movie2actor e : movie2actor) {
            String mid = e.mid;
            mid=mid.replace("'","\\'");
            int aid = e.aid;
            /*PreparedStatement ps = conn.prepareStatement("insert into movie2actor (id,mid,aid) values(?,?,?)");
            ps.setInt(1, i + 1);
            ps.setString(2, mid);
            ps.setInt(3, aid);
            ps.executeUpdate();
            ps.close();*/
            sb.append("(" + i + ",'" + mid + "'," + aid + "),");
            if (i % 200 == 0) {
                //conn.commit();
                log.info("insert into movie2actor (id,mid,aid) values" + sb.substring(0, sb.length() - 1)+";");
                sb = new StringBuffer();
            }
            i++;
        }
        //conn.commit();
        log.info("insert into movie2actor (id,mid,aid) values" + sb.substring(0, sb.length() - 1)+";");
    }

    public static void main(String[] args) {
        ItvDataProcess prs = new ItvDataProcess();
        try {
            prs.find("select id ,actor from movie");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

class Movie2actor {
    public Movie2actor(String mid, int aid) {
        this.mid = mid;
        this.aid = aid;
    }

    public String mid;
    public int aid;
}
